"""sql 查询文件"""
import calendar
import logging
import time
import datetime

from database.connection import Link_database
# from ydsp.database.connection import Link_database

from dateutil.relativedelta import relativedelta


from dysms_python.demo_sms_send import send_sms

logger = logging.getLogger(__name__)



def sel_generaoperattube(page):
    """代运营项目管理"""
    con, obj = Link_database()

    sql = """select id,store_name, project_type, chief_inspecto,director,operator,start_time,end_time,account_entry,amount_account,renewal_time,customer_info,subscription_info,contract_status,store_status,email from genera_operat_tube limit %d,20""" % (page)

    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q

class submit_expense(object):
    """报销查询"""
    def __init__(self, bx_name, bx_category, department):
        self.bx_name = bx_name
        self.bx_category = bx_category
        self.department = department
        self.start = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m'), '%Y-%m')))
        self.end = int(time.mktime(time.strptime(datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')))
        self.con, self.obj = Link_database()

    def statdate(self, start, end):
        if self.start and self.end:
            self.start = time.mktime(time.strptime(start, '%Y-%m-%d'))
            self.end = time.mktime(time.strptime(end, '%Y-%m-%d'))

    # def user_list(self):
    #     if self.department:
    #         sql = """select id from user where department=%d""" % self.department
    #         self.obj.execute(sql)
    #         q = self.obj.fetchall()
    #         u_list = [0]
    #         for i in q:
    #             u_list.append(i[0])
    #         self.u_tuple = tuple(u_list)

    def statements(self):
        sql = """select a.id,a.statdate,b.department,a.bx_name,a.bx_category,a.bx_money,a.file_address,a.note from submit_expense as a left join depar as b on a.bx_department=b.id where a.statdate between {} and {}""".format(self.start, self.end)
        if self.bx_name:
            sql = """select a.id,a.statdate,b.department,a.bx_name,a.bx_category,a.bx_money,a.file_address,a.note from submit_expense as a left join depar as b on a.bx_department=b.id where a.bx_name='{}' and a.statdate between {} and {}""".format(self.bx_name, self.start, self.end)
        elif self.department:
            sql = """select a.id,a.statdate,b.department,a.bx_name,a.bx_category,a.bx_money,a.file_address,a.note from submit_expense as a left join depar as b on a.bx_department=b.id where a.bx_department={} and a.statdate between {} and {}""".format(self.department, self.start, self.end)
        elif self.bx_category:
            sql = """select a.id,a.statdate,b.department,a.bx_name,a.bx_category,a.bx_money,a.file_address,a.note from submit_expense as a left join depar as b on a.bx_department=b.id where a.bx_category='{}' and a.statdate between {} and {}""".format(self.bx_category, self.start, self.end)

        return sql

    def mian(self):
        # self.user_list()
        sql = self.statements()
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def __del__(self):
        self.obj.close()
        self.con.close()


def expense(start, end):
    """退款查询"""
    con, obj = Link_database()

    sql = """select id,statdate,shop_name,refund_time,refund_money,note from expense where statdate between %d and %d""" % (start, end)
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def financial_package(page):
    """查询财务报表"""
    con, obj = Link_database()

    sql = """select id,statdate,file_name,file_address from financial limit %d,20""" % page
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


class personnel_wages(object):
    """查询工资"""
    def __init__(self, user_id, department, start_time, end_time, is_start):
        self.user_id = user_id
        self.is_start = is_start
        self.department = department
        self.start_time = start_time
        self.end_time = end_time
        self.con, self.obj = Link_database()

    def sel_data(self):
        """查询"""
        if self.department:
            sql = """select a.id,a.statdate,a.salary,a.basis_salary,a.full_time,a.be_attendance,a.real_attendance,a.birthday_allowance,
                                a.subsidies,a.late,a.ask_leave,a.leave_cost,a.work_overtime,a.work_overtime_cost,a.business_allowance,
                                a.service_fee,a.sales_some,a.performance,a.social,a.company_social,a.real_wages,b.name,b.jobs from wage as a inner join 
                                user as b on a.user_id=b.id where b.department={} and a.statdate between {} and {}"""\
                                .format(self.department, self.start_time, self.end_time)
        else:
            sql = """select a.id,a.statdate,a.salary,a.basis_salary,a.full_time,a.be_attendance,a.real_attendance,a.birthday_allowance,
                            a.subsidies,a.late,a.ask_leave,a.leave_cost,a.work_overtime,a.work_overtime_cost,a.business_allowance,
                            a.service_fee,a.sales_some,a.performance,a.social,a.company_social,a.real_wages,b.name,b.jobs from wage as a inner join 
                            user as b on a.user_id=b.id where a.statdate between {} and {}""".format(self.start_time, self.end_time)

        if self.user_id:
            if self.is_start:
                sql = """select a.id,a.statdate,a.salary,a.basis_salary,a.full_time,a.be_attendance,a.real_attendance,a.birthday_allowance,
                                                a.subsidies,a.late,a.ask_leave,a.leave_cost,a.work_overtime,a.work_overtime_cost,a.business_allowance,
                                                a.service_fee,a.sales_some,a.performance,a.social,a.company_social,a.real_wages,b.name,b.jobs from wage as a inner join 
                                                user as b on a.user_id=b.id where a.user_id={} and a.statdate between {} and {}""" \
                                                .format(self.user_id, self.start_time, self.end_time)
            else:
                sql = """select a.id,a.statdate,a.salary,a.basis_salary,a.full_time,a.be_attendance,a.real_attendance,a.birthday_allowance,
                                                                a.subsidies,a.late,a.ask_leave,a.leave_cost,a.work_overtime,a.work_overtime_cost,a.business_allowance,
                                                                a.service_fee,a.sales_some,a.performance,a.social,a.company_social,a.real_wages,b.name,b.jobs from wage as a inner join 
                                                                user as b on a.user_id=b.id where a.user_id={}""" \
                                                                .format(self.user_id)

        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def sum_data(self):
        """总和"""
        if self.department:
            sql = """select sum(real_wages),sum(company_social) from wage as a inner join user as b on a.user_id=b.id where b.department={} and a.statdate between {} and {}""".format(self.department, self.start_time, self.end_time)
        else:
            sql = """select sum(real_wages),sum(company_social) from wage where statdate between {} and {}""".format(self.start_time, self.end_time)

        if self.user_id:
            if self.is_start:
                sql = """select sum(real_wages),sum(company_social) from wage where user_id={} and statdate between {} and {}""".format(self.user_id, self.start_time, self.end_time)
            else:
                sql = """select sum(real_wages),sum(company_social) from wage where user_id={}""".format(self.user_id)

        self.obj.execute(sql)
        n = self.obj.fetchone()

        return n

    def deal_with(self):
        """数据"""
        q = self.sel_data()
        number, social_sum = self.sum_data()
        details = []
        for i in q:
            a = {
                "id": i[0],
                "statdate": time.strftime('%Y-%m', time.localtime(i[1])),
                "salary": i[2],
                "basis_salary": i[3],
                "full_time": i[4],
                "be_attendance": i[5],
                "real_attendance": i[6],
                "birthday_allowance": i[7] if i[7] != '0' else '',
                "subsidies": i[8] if i[8] != '0' else '',
                "late": i[9] if i[9] != '0' else '',
                "ask_leave": i[10] if i[10] != '0' else '',
                "leave_cost": i[11] if i[11] != '0.0' else '',
                "work_overtime": i[12] if i[12] != '0' else '',
                "work_overtime_cost": i[13] if i[13] != '0' else '',
                "business_allowance": i[14] if i[14] != '0' else '',
                "service_fee": i[15] if i[15] != '0' else '',
                "sales_some": i[16] if i[16] != '0' else '',
                "performance": i[17] if i[17] != '0' else '',
                "social": i[18] if i[18] != '0' else '',
                "company_social": i[19] if i[19] != '0' else '',
                "real_wages": i[20],
                "name": i[21],
                "jods": i[22]
            }
            details.append(a)

        data = {"details": details, "number": number, "social_sum": social_sum}

        return data

    def __del__(self):
        self.obj.close()
        self.con.close()


def to_deducted(start_time, end_time):
    """待扣款查询"""
    con, obj = Link_database()

    sql = """select id,statdate,company,project,money,take_back,collection_date,collection_account from to_deducted where statdate between %d and %d""" % (start_time, end_time)
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q

def submit_type():
    """报销类别查询"""
    con, obj = Link_database()

    sql = """select species from submit_type"""
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


def income_type():
    """收入类别查询"""
    con, obj = Link_database()

    sql = """select id,category from income_type"""
    obj.execute(sql)
    q = obj.fetchall()

    obj.close()
    con.close()

    return q


class sum_income(object):
    """总收入查询"""
    def __init__(self, category, start_time, end_time, is_start):
        self.category = category
        self.is_start = is_start
        self.start_time = start_time
        self.end_time = end_time + 1
        self.con, self.obj = Link_database()

    def sel_details(self):
        """详情查询"""
        if self.category:
            sql = """select id,statdate,category,project_name,signing_time,received_service_day,start_time,end_time,service_money,operation,month_service_day,already_paid,month_service_money,a_service_day,remaining_day,a_service_money,remaining_money,note from sum_income where category='{}' and statdate between {} and {}""".format(self.category, self.start_time, self.end_time)
        else:
            sql = """select id,statdate,category,project_name,signing_time,received_service_day,start_time,end_time,service_money,operation,month_service_day,already_paid,month_service_money,a_service_day,remaining_day,a_service_money,remaining_money,note from sum_income where statdate between {} and {}""".format(self.start_time, self.end_time)

        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def sel_sum(self):
        """总和统计"""
        if self.category:
            sql = """select sum(month_service_money) from sum_income where category='{}' and statdate between {} and {}""".format(self.category, self.start_time, self.end_time)
        else:
            sql = """select sum(month_service_money) from sum_income where statdate between {} and {} """.format(self.start_time, self.end_time)

        self.obj.execute(sql)
        s = self.obj.fetchone()[0]
        if not s:
            s = 0

        return s

    def figure(self):
        """每个月数据和"""
        years = time.strftime('%Y', time.localtime(time.time()))
        start_years = time.mktime(time.strptime(years + '-01', '%Y-%m'))
        end_years = time.mktime(time.strptime(years + '-12', '%Y-%m'))

        if self.is_start:
            sql = """select statdate,sum(month_service_money) from sum_income where statdate between {} and {} group by statdate""".format(self.start_time, self.end_time)
        else:
            sql = """select statdate,sum(month_service_money) from sum_income where statdate between {} and {} group by statdate""".format(start_years, end_years)
        self.obj.execute(sql)
        q = self.obj.fetchall()
        sum_month = []
        for i in q:
            a = {
                "key": time.strftime('%Y-%m', time.localtime(i[0])),
                "value": i[1]
            }
            sum_month.append(a)

        return sum_month

    def mian(self):
        details = self.deal_with()
        number = self.sel_sum()
        sum_month = self.figure()

        data = {"details": details, "number": number, "sum_month": sum_month}

        return data

    def deal_with(self):
        """数据整理"""
        q = self.sel_details()
        details = []
        for i in q:
            a = {
                "id": i[0],
                "statdate": time.strftime('%Y-%m', time.localtime(i[1])),
                "category": i[2],
                "project_name": i[3],
                "signing_time": i[4],
                "received_service_day": i[5],
                "start_time": i[6],
                "end_time": i[7],
                "service_money": i[8],
                "operation": i[9],
                "month_service_day": i[10],
                "already_paid": i[11],
                "month_service_money": i[12],
                "a_service_day": i[13],
                "remaining_day": i[14],
                "a_service_money": i[15],
                "remaining_money": i[16],
                "note": i[17]
            }
            details.append(a)

        return details

    def __del__(self):
        self.obj.close()
        self.con.close()


class send_SMS(object):
    """发送短信"""
    def __init__(self, month_time):
        self.month_time = month_time
        self.con, self.obj = Link_database()

    def sel_data(self):
        """查询"""
        sql = """select b.phone,b.name,a.statdate,a.salary,a.full_time,a.real_attendance,a.birthday_allowance,a.subsidies,a.late,a.ask_leave,a.leave_cost,a.work_overtime,a.work_overtime_cost,a.business_allowance,a.service_fee,a.sales_some,a.performance,a.social,a.real_wages from wage as a inner join user as b on a.user_id=b.id where a.statdate={}""".format(self.month_time)
        self.obj.execute(sql)
        q = self.obj.fetchall()
        return q

    def deal_with(self):
        """处理"""
        q = self.sel_data()
        """亲爱的${name}员工你好，${time}月工资已统计完毕，薪资：${salary}，全勤奖：${full_time}，出勤：${real}天，生日津贴：${birthday}，饭补：${subsidies}，迟到扣款：${late}，请假天数：${ask}，请假费用${leave}，加班天数${work}，加班费：${cost}，出差补贴：${business}，服务提成：${service}，销售提点：${sales}，绩效：${performance}，社保：${social}，实发工资${wages}。如有疑问请尽快联系财务！"""
        for i in q:
            a = {
                "name": i[1],
                "time": time.strftime('%Y-%m', time.localtime(i[2])),
                "salary": i[3],
                "full_time": i[4],
                "real": i[5],
                "birthday": i[6],
                "subsidies": i[7],
                "late": i[8],
                "ask": i[9],
                "leave": i[10],
                "work": i[11],
                "cost": i[12],
                "business": i[13],
                "service": i[14],
                "sales": i[15],
                "performance": i[16],
                "social": i[17],
                "wages": i[18]
            }
            send_sms(i[0], "点将台", "SMS_153996115", a)

    def __del__(self):
        self.obj.close()
        self.con.close()


class user_wage(object):
    """工资录入页面查询"""
    def __init__(self):
        self.con, self.obj = Link_database()

    def sel_data(self):
        times = str(datetime.date.today() - relativedelta(months=+1))[:-3]
        years, month = times.split('-')
        day = calendar.monthrange(int(years), int(month))[1]

        stat_date = int(time.mktime(time.strptime(times + '-01', '%Y-%m-%d')))
        end_date = int(time.mktime(time.strptime(times + '-' + str(day), '%Y-%m-%d')))

        # sql = """select a.id,a.name,a.department,a.jobs,b.basis_salary,b.subsidies,b.business_allowance,b.service_fee,b.sales_some,b.performance,b.social,b.company_social from user as a left join wage as b on a.id=b.user_id"""
        sql = """select c.*,d.department from depar as d right join (select a.id,a.name,a.department,a.jobs,b.basis_salary,b.subsidies,b.business_allowance,b.service_fee,b.sales_some,b.performance,b.social,b.company_social from user as a left join (select user_id,basis_salary,subsidies,business_allowance,service_fee,sales_some,performance,social,company_social from wage where statdate between {} and {}) as b on a.id=b.user_id) as c on d.id=c.department""".format(stat_date, end_date)
        self.obj.execute(sql)
        q = self.obj.fetchall()

        return q

    def deal_with(self):
        q = self.sel_data()
        data = []

        for i in q:
            if i[0] != 1:
                data.append({
                    "id": i[0],
                    "name": i[1],
                    "department": i[12],
                    "jobs": i[3],
                    "basis_salary": i[4] if i[4] else '0',
                    "subsidies": i[5] if i[5] else '0',
                    # "business_allowance": i[6] if i[6] else '0',
                    "service_fee": i[7] if i[7] else '0',
                    "sales_some": i[8] if i[8] else '0',
                    "performance": i[9] if i[9] else '0',
                    "social": i[10] if i[10] else '0',
                    "company_social": i[11] if i[11] else '0'
                })

        return data
    
    def __del__(self):
        self.obj.close()
        self.con.close()